Operator

Example

Comparison Operators

Equality (=)

select x from Student x where x.sid = 1

Non-equality(<>)

select x from Student x where x.sid <> 1

Less-than and Lesser/equal to values (<,<=)

select x from Student x where x.sid < 2

Greater-than and greater/equal to values(>,>=)

select x from Student x where x.sid > 2

Logical Operators

NULL VALUE TEST OPERATOR – IS NULL

select x from Student x where x.sid is null

RANGE TEST OPERATOR – BETWEEN

select x from Student x where x.sid between 1 and 3

SET MEMBERSHIP TEST OPERATOR – IN

select x from Student x where x.sname in ('Manu Manjunatha', 'Advith Tyagraj')

PATTERN MATCHING OPERATOR – LIKE

select x from Student x where x.sname like('Manu%')

EXISTS

select x from Student x where exists(select x from Student x where x.sid = 1)

ALL

select x from Student x where x.sid < all(select x.sid from Student x where x.sid = 2)

ANY

select x from Student x where x.sid < any(select x.sid from Student x where x.sid = 2)

Conjunctive

AND

select x from Student x where x.sid = 1 and x.sid = 3

OR

select x from Student x where x.sid = 1 or x.sid = 3

Negative

<>, != (Not Equal)

select x from Student x where x.sid <> 1

select x from Student x where x.sid  != 1

IS NOT NULL

select x from Student x where x.sid is not null

NOT BETWEEN

select x from Student x where x.sid not between 1 and 3

NOT IN

select x from Student x where x.sname not in ('Manu Manjunatha', 'Advith Tyagraj')

NOT LIKE

select x from Student x where x.sname not like('Manu%')

NOT EXISTS

select x from Student x where not exists(select x from Student x where x.sid = 1)

Arithmetic

+

select x from Student x where x.sid = 1+1

-

select x from Student x where x.sid = 3-1

*

select x from Student x where x.sid = 3*1

/

select x from Student x where x.sid = 4/2

Function

Example

CONCAT(string1, string2): Concatenates two string fields or literals.

select x from Student x where CONCAT(x.name, 's') = 'Adviths'

SUBSTRING(string, startIndex, [length]): Returns the part of the string argument starting at

startIndex (1-based) and optionally ending at length characters past startIndex. If the length argument is not

specified, the substring from the startIndex to the end of the string is returned.

select x from Student x where SUBSTRING(x.name, 1, 1) = 'M'

TRIM([LEADING | TRAILING | BOTH] [character FROM] string: Trims the specified character from either the beginning ( LEADING ) end ( TRAILING) or both ( BOTH ) of the string argument. If no trim character is specified, the space character will be trimmed.

select x from Student x where TRIM(BOTH 'Z' FROM x.title) = 'Advith'

LOWER(string): Returns the lower-case of the specified string argument.

select x from Student x where LOWER(x.name) = 'advith'

UPPER(string): Returns the upper-case of the specified string argument.

select x from Student x where UPPER(x.name) = 'ADVITH'

LENGTH(string): Returns the number of characters in the specified string argument.

select x from Student x where LENGTH(x.name) = 6

LOCATE(searchString, candidateString [, startIndex]): Returns the first index of searchString in

candidateString. Positions are 1-based. If the string is not found, returns 0.

select x from Student x where LOCATE('D', x.name) = 2

ABS(number): Returns the absolute value of the argument.

select x from Student x where ABS(x.sid) >= 1.00

SQRT(number): Returns the square root of the argument.

select x from Student x where SQRT(x.sid) >= 1.00

MOD(number, divisor): Returns the modulo of number and divisor.

select x from Student x where MOD(x.sid, 5) = 0

INDEX(identification_variable): Returns an integer value corresponding to the position of its argument in an ordered list. The INDEX function can only be applied to identification variables denoting types for which an order column has been specified.

In the following example, studentWaitlist is a list of students for which an order column has been specified, the query returns the name of the first student on the list of the course named 'Calculus':

SELECT w.name FROM Course c JOIN c.studentWaitlist w WHERE c.name = ‘Calculus’ AND INDEX(w) = 0

CURRENT_DATE: Returns the current date.

CURRENT_TIME: Returns the current time. CURRENT_TIMESTAMP: Returns the current timestamp.

select x, CURRENT_DATE from Student x

select x, CURRENT_TIME from Student x

select x, CURRENT_TIMESTAMP from Student x

min: Returns the minimum of selected values

max: Returns the maximum of selected values

avg: Returns the average of selected values

count: Returns the count of selected values

SELECT min(x.price) FROM Item x

SELECT max(x.price) FROM Item x

SELECT avg(x.price) FROM Item x

SELECT count(x) FROM Item x